<?php
/*
 +----------------------------------------------------------------------
 + Title        : ExtendExcel 控制器
 + Author       : Randy_chen
 + Version      : V1.0.0.1
 + Initial-Time : 2021/8/23 20:54
 + Last-time    : 2021/8/23 20:54+ chen2
 + Desc         : ExtendExcel
 +----------------------------------------------------------------------
*/


namespace randy\tools\units;


use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use think\facade\Filesystem;

class ExtendExcel
{
    /**
     * @throws Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function import($fieldArr=[], $file_name = 'file')
    {
        $file = request()->file($file_name);
        if ($file) {
            $info = $file->move('./uploads');
            if ($info) {
                $filename = $info->getRealPath();
                $exts = $info->getExtension();
                if ($exts == 'xls') {
                    $PHPReader = IOFactory::createReader(IOFactory::READER_XLS);
                } else {
                    $PHPReader = IOFactory::createReader(IOFactory::READER_XLSX);
                }
                //载入文件
                $PHPExcel = $PHPReader->load($filename);
                $currentSheet = $PHPExcel->getSheet(0);

                $allColumn = $currentSheet->getHighestColumn();
                $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
                $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);


                for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)
                                            ->getValue();
                        $fields[] = $val;
                    }
                }
                $insert = [];
                for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                    $values = [];
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)
                                            ->getFormattedValue();
                        $values[] = is_null($val) ? '' : $val;
                    }
                    $row = [];
                    if (empty(array_filter($values))) {
                        continue;
                    }
                    $temp = array_combine($fields, $values);
                    if($fieldArr){
                        foreach ($temp as $k => $v) {
                            if (isset($fieldArr[$k]) && $k !== '') {
                                $row[$fieldArr[$k]] = $v;
                            }
                        }
                    }else{
                        $row = $temp;
                    }

                    if ($row) {
                        $insert[] = $row;
                    }
                }
                return $insert;
            }
        }
    }

    /**
     * EXCEL导出
     *
     * @throws Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    static function export($result, $field, $file = 'xx', $sheetName = 'sheet1')
    {
        $field = array_merge(['index'=>'序号'] ,$field);
        foreach ($result as $index=>&$item){
            $item['index'] = $index+1;
        }
        ini_set('memory_limit', '1024M');
        set_time_limit(600);//设置超时限制为６分钟
        $column = [
            'A',
            'B',
            'C',
            'D',
            'E',
            'F',
            'G',
            'H',
            'I',
            'J',
            'K',
            'L',
            'M',
            'N',
            'O',
            'P',
            'Q',
            'R',
            'S',
            'T',
            'U',
            'V',
            'W',
            'X',
            'Y',
            'Z',
            'AA',
            'AB',
            'AC',
            'AD',
            'AE',
            'AF',
            'AG',
            'AH'
        ];
        $objPHPExcel = new Spreadsheet();
        $objPHPExcel->getActiveSheet()->freezePane('A2');
        //设置自动宽高
        foreach ($column as $v) {
            $objPHPExcel->getActiveSheet()
                        ->getColumnDimension($v)
                        ->setAutoSize(true);
        }
        $filedIndex = 0;
        $min = 'A';
        $max =  $column[count($field)-1];
        foreach ($field as $v) {
            $objPHPExcel->getActiveSheet()
                        ->setCellValue(sprintf('%s%u', $column[$filedIndex], '1'), $v);

            $filedIndex++;
        }
        $objPHPExcel->getActiveSheet()->getStyle("{$min}1:{$max}1")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('C5D9F1');

        //循环设置行
        $num = 1;
        foreach ($result as $vv) {
            $num += 1;
            $cell = $objPHPExcel->setActiveSheetIndex(0);
            $index = 0;
            foreach ($field as $kkk => $vvv) {
                $keys = explode('.', $kkk);
                if (count($keys) == 1) {
                    if (is_numeric($v)) {
                        $cell->setCellValueExplicit(sprintf('%s%u', $column[$index], $num), $vv[$kkk] . "\t", DataType::TYPE_STRING);
                    } else {
                        $cell->setCellValueExplicit(sprintf('%s%u', $column[$index], $num), $vv[$kkk], DataType::TYPE_STRING);
                    }
                } elseif (count($keys) == 2) {
                    $cell->setCellValueExplicit(sprintf('%s%u', $column[$index], $num), $vv[$keys[0]] ? $vv[$keys[0]][$keys[1]] : '', DataType::TYPE_STRING);
                }

                $index++;
            }
        }
        //设置单元格字体
        $objPHPExcel->getDefaultStyle()
                    ->getAlignment()
                    ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()
                    ->setTitle($sheetName);
        $objPHPExcel->setActiveSheetIndex(0);

        $filename = $file . '-' . date("Ymd");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment;filename={$filename}.xlsx");
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Xlsx');
        // return ajax('成功');
        $objWriter->save('php://output');
        exit;
    }
}